﻿using Microsoft.AspNetCore.Http;
using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;

namespace SX.Core
{
	/// <summary>
	/// Excel导入导出
	/// </summary>
	public class ExcelHelper
	{
		public static IWorkbook workbook = null;

		/// <summary>
		/// 加载excel 使用下面方法之前必须先执行此方法
		/// </summary>
		/// <returns></returns>
		public static void Load(string filename, MemoryStream ms)
		{
			if (filename.IndexOf(".xlsx") > 0) // 2007版本
			{
				workbook = new XSSFWorkbook(ms);
			}
			else if (filename.IndexOf(".xls") > 0) // 2003版本
				workbook = new HSSFWorkbook(ms);
		}

		/// <summary>
		/// 获取页面数量
		/// </summary>
		/// <returns></returns>
		public static int GetSheetsCount()
		{
			if (null == workbook)
				return 0;
			return workbook.NumberOfSheets;
		}

		/// <summary>
		/// 获取页面名称
		/// </summary>
		/// <param name="index"></param>
		/// <returns></returns>
		public static string GetSheetName(int index)
		{
			if (null == workbook)
				return "";

			return workbook.GetSheetName(index);
		}

		#region excel导入

		/// <summary>
		/// 将excel中的数据导入到DataTable中
		/// </summary>
		/// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
		/// <returns>返回的DataTable</returns>
		public static DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn = true)
		{
			ISheet sheet = null;
			DataTable data = new DataTable();
			int startRow = 0;
			try
			{
				//加载excel文件流
				//Load(filename);

				if (sheetName != null)
				{
					sheet = workbook.GetSheet(sheetName);
					if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
					{
						sheet = workbook.GetSheetAt(0);
					}
				}
				else
				{
					sheet = workbook.GetSheetAt(0);
				}
				if (sheet != null)
				{
					IRow firstRow = sheet.GetRow(0);
					int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数

					if (isFirstRowColumn)
					{
						for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
						{
							ICell cell = firstRow.GetCell(i);
							if (cell != null)
							{
								string cellValue = cell.StringCellValue;
								if (cellValue != null)
								{
									DataColumn column = new DataColumn(cellValue);
									data.Columns.Add(column);
								}
							}
						}
						startRow = sheet.FirstRowNum + 1;
					}
					else
					{
						startRow = sheet.FirstRowNum;
					}

					//最后一列的标号
					int rowCount = sheet.LastRowNum;
					for (int i = startRow; i <= rowCount; ++i)
					{
						IRow row = sheet.GetRow(i);
						if (row == null) continue; //没有数据的行默认是null　　　　　　　

						DataRow dataRow = data.NewRow();
						for (int j = row.FirstCellNum; j < cellCount; ++j)
						{
							ICell cell = row.GetCell(j);
							if (cell != null) //同理，没有数据的单元格都默认是null
							{
								if (cell.CellType == CellType.Formula) //包含公式
									dataRow[j] = cell.StringCellValue;
								else if (cell.CellType == CellType.Numeric)
								{
									if (HSSFDateUtil.IsCellDateFormatted(cell))
										dataRow[j] = cell.DateCellValue;
									else
										dataRow[j] = cell.ToString();
								}
								else
									dataRow[j] = cell.ToString();
							}
						}
						data.Rows.Add(dataRow);
					}
				}

				return data;
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}







		#endregion


		#region excel导出

		/// <summary>
		/// 导出Excel HSSFWorkbook 
		/// </summary>
		/// <param name="dtSource"></param>
		/// <param name="strHeaderText"></param>
		/// <returns></returns>
		public static HSSFWorkbook ExportExcelHSSF(DataTable dtSource, string strHeaderText, string mapPath="", int imgIndex = 9)
		{
			HSSFWorkbook workbook = new HSSFWorkbook();
			ISheet sheet;
			if (string.IsNullOrEmpty(dtSource.TableName))
				sheet = workbook.CreateSheet();
			else
				sheet = workbook.CreateSheet(dtSource.TableName) as HSSFSheet;

			#region 右击文件 属性信息
			{
				DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
				workbook.DocumentSummaryInformation = dsi;
				SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
				si.Author = ""; //填加xls文件作者信息
				si.ApplicationName = ""; //填加xls文件创建程序信息
				si.LastAuthor = ""; //填加xls文件最后保存者信息
				si.Comments = ""; //填加xls文件作者信息
				si.Title = ""; //填加xls文件标题信息
				si.Subject = ""; //填加文件主题信息
				si.CreateDateTime = DateTime.Now;
				workbook.SummaryInformation = si;
			}
			#endregion

			ICellStyle headStyle = GetHeadStyle(workbook, true);
			ICellStyle cellStyle = GetCellStyle(workbook, true);
			cellStyle.WrapText = true;//自动换行
			cellStyle.Alignment = HorizontalAlignment.Left;
			cellStyle.VerticalAlignment = VerticalAlignment.Center;
			//日期格式
			ICellStyle cellDateTimeStyle = GetCellDateTimeStyle(workbook, true);
			Dictionary<string, ICellStyle> dicCellDateTimeStyle = new Dictionary<string, ICellStyle>();
			dicCellDateTimeStyle.Add("DateTime", cellDateTimeStyle);

			//行高
			float heightInPoints = 50f;

			//列宽 取得数据源最大列宽 用于设置Excel列宽
			int[] arrColValWidth = GetDataColumnValueWidth(dtSource);

			int rowIndex = 0;
			foreach (DataRow row in dtSource.Rows)
			{
				#region 设置列 填充标题

				if (rowIndex == 0 || rowIndex == 65535)
				{
					if (rowIndex != 0)  //大于sheet行数
					{
						sheet = workbook.CreateSheet();
						rowIndex = 0;
					}

					if (rowIndex == 0)
					{
						IRow headerRow = sheet.CreateRow(0);
						headerRow.HeightInPoints = heightInPoints;

						//列头及样式
						if (string.IsNullOrEmpty(strHeaderText))
						{
							foreach (DataColumn column in dtSource.Columns)
							{
								headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
								headerRow.GetCell(column.Ordinal).CellStyle = headStyle;

								//设置列宽
								sheet.SetColumnWidth(column.Ordinal, Math.Min(60, (arrColValWidth[column.Ordinal] + 1)) * 256);
							}
						}
						else
						{
							var lstColumnName = strHeaderText.Trim().ToList(",");//   StringHelper.StringToList(strHeaderText.Trim(), ",");
							for (int i = 0; i < dtSource.Columns.Count; i++)
							{
								if (lstColumnName.Count > i)
									headerRow.CreateCell(dtSource.Columns[i].Ordinal).SetCellValue(lstColumnName[i]);
								else
									headerRow.CreateCell(dtSource.Columns[i].Ordinal).SetCellValue("列名" + (i + 1).ToString());

								headerRow.GetCell(dtSource.Columns[i].Ordinal).CellStyle = headStyle;

								//设置列宽
								sheet.SetColumnWidth(dtSource.Columns[i].Ordinal, Math.Min(60, (arrColValWidth[dtSource.Columns[i].Ordinal] + 1)) * 256);
							}
						}
					}
					rowIndex = 1;
				}

				#endregion

				#region 填充数据

				IRow dataRow = sheet.CreateRow(rowIndex);
				dataRow.HeightInPoints = heightInPoints;
				foreach (DataColumn column in dtSource.Columns)
				{
					ICell newCell = dataRow.CreateCell(column.Ordinal);
					string value = row[column].ToString();
					string columnType = column.DataType.ToString();

					newCell.CellStyle = cellStyle;


					if (column.ColumnName != "Originaldata" && column.ColumnName != "Finaldata")
					{

						SetCellValue(workbook, newCell, columnType, value, dicCellDateTimeStyle, rowIndex);
					}
					//如果是base64位图片数据
					if ((column.ColumnName == "Originaldata" && value != ""))
					{
						SetCellValue(workbook, newCell, "image", value, dicCellDateTimeStyle, rowIndex, imgIndex, mapPath);
						sheet.SetColumnWidth(dtSource.Columns.IndexOf(column), 15 * 256);
					}
					if ((column.ColumnName == "Finaldata" && value != ""))
					{
						SetCellValue(workbook, newCell, "image", value, dicCellDateTimeStyle, rowIndex, imgIndex + 1, mapPath);
						sheet.SetColumnWidth(dtSource.Columns.IndexOf(column), 15 * 256);
					}

				}

				#endregion

				rowIndex++;
			}
			return workbook;
		}

		/// <summary>
		/// 导出excel XSSFWorkbook
		/// </summary>
		/// <param name="dtSource"></param>
		/// <param name="strHeaderText"></param>
		/// <returns></returns>
		public static XSSFWorkbook ExportExcelXSSF(DataTable dtSource, string strHeaderText)
		{
			XSSFWorkbook workbook = new XSSFWorkbook();
			XSSFSheet sheet = workbook.CreateSheet() as XSSFSheet;

			XSSFCellStyle dateStyle = workbook.CreateCellStyle() as XSSFCellStyle;
			XSSFDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat;
			dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

			//取得列宽
			int[] arrColWidth = new int[dtSource.Columns.Count];
			foreach (DataColumn item in dtSource.Columns)
			{
				arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
			}
			for (int i = 0; i < dtSource.Rows.Count; i++)
			{
				for (int j = 0; j < dtSource.Columns.Count; j++)
				{
					int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
					if (intTemp > arrColWidth[j])
					{
						arrColWidth[j] = intTemp;
					}
				}
			}
			int rowIndex = 0;

			foreach (DataRow row in dtSource.Rows)
			{
				#region 新建表，填充表头，填充列头，样式

				if (rowIndex == 0)
				{
					#region 列头及样式
					{
						XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow;


						XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
						headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
						XSSFFont font = workbook.CreateFont() as XSSFFont;
						font.FontHeightInPoints = 10;
						font.Boldweight = 700;
						headStyle.SetFont(font);


						foreach (DataColumn column in dtSource.Columns)
						{
							headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
							headerRow.GetCell(column.Ordinal).CellStyle = headStyle;

							//设置列宽
							sheet.SetColumnWidth(column.Ordinal, Math.Min(255, (arrColWidth[column.Ordinal] + 1)) * 256);

						}
					}
					#endregion

					rowIndex = 1;
				}

				#endregion

				#region 填充内容

				XSSFRow dataRow = sheet.CreateRow(rowIndex) as XSSFRow;
				foreach (DataColumn column in dtSource.Columns)
				{
					XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell;

					string value = row[column].ToString();
					string columnType = column.DataType.ToString();
					SetCellValue(workbook, newCell, columnType, value, null, rowIndex);
				}

				#endregion

				rowIndex++;
			}
			return workbook;
		}


		/// <summary>
		/// 判断是否数字
		/// </summary>
		/// <param name="message"></param>
		/// <param name="result"></param>
		/// <returns></returns>
		static bool isNumeric(String message, out double result)
		{
			Regex rex = new Regex(@"^[-]?\d+[.]?\d*$");
			result = -1;
			if (rex.IsMatch(message))
			{
				result = double.Parse(message);
				return true;
			}
			else
				return false;

		}


		/// <summary>
		/// 获取Cell样式 DateTime
		/// </summary>
		/// <param name="workbook"></param>
		static ICellStyle GetCellDateTimeStyle(IWorkbook workbook, bool isFrame = false, string format = "yyyy-MM-dd hh:mm")
		{
			ICellStyle cellStyle = workbook.CreateCellStyle();

			if (isFrame)
			{
				cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
				cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
				cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
				cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
			}

			IFont font = workbook.CreateFont();
			font.FontHeightInPoints = 10;
			font.FontName = "宋体";
			cellStyle.SetFont(font);
			cellStyle.Alignment = HorizontalAlignment.Left;
			cellStyle.VerticalAlignment = VerticalAlignment.Center;
			IDataFormat dataFormat = workbook.CreateDataFormat();
			cellStyle.DataFormat = dataFormat.GetFormat(format);
			return cellStyle;
		}
		/// <summary>
		/// 获取Head样式
		/// </summary>
		/// <param name="workbook"></param>
		static ICellStyle GetHeadStyle(IWorkbook workbook, bool isFrame = false)
		{
			ICellStyle headStyle = workbook.CreateCellStyle();

			if (isFrame)
			{
				headStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
				headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
				headStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
				headStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
			}

			headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
			IFont font = workbook.CreateFont();
			font.FontHeightInPoints = 10;
			font.Boldweight = 700;
			font.FontName = "宋体";
			headStyle.SetFont(font);
			return headStyle;
		}


		/// <summary>
		/// 获取Cell样式
		/// </summary>
		/// <param name="workbook"></param>
		static ICellStyle GetCellStyle(IWorkbook workbook, bool isFrame = false)
		{
			ICellStyle cellStyle = workbook.CreateCellStyle();

			if (isFrame)
			{
				cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
				cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
				cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
				cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
			}

			IFont font = workbook.CreateFont();
			font.FontHeightInPoints = 10;
			font.FontName = "宋体";
			cellStyle.SetFont(font);
			return cellStyle;
		}



		/// <summary>
		/// 设置单元格文本
		/// </summary>
		/// <param name="cell"></param>
		/// <param name="columnType"></param>
		/// <param name="fieldValue"></param>
		static void SetCellValue(IWorkbook workbook, ICell cell, string columnType, string fieldValue, Dictionary<string, ICellStyle> dicCellDateTimeStyle, int rowindex, int colIndex = 0, string path = "")
		{
			cell.CellStyle.DataFormat = 0; //重置
			switch (columnType)
			{
				case "System.String": //字符串类型
					double result;
					if (isNumeric(fieldValue, out result))
					{
						double.TryParse(fieldValue, out result);
						cell.SetCellValue(result);
						break;
					}
					else
					{
						cell.SetCellValue(fieldValue);
						break;
					}
				case "System.DateTime": //日期类型
					DateTime dateV;
					DateTime.TryParse(fieldValue, out dateV);
					if (dateV == DateTime.MinValue)
						cell.SetCellValue("");
					else
						cell.SetCellValue(dateV);

					if (dicCellDateTimeStyle.ContainsKey("DateTime"))
						cell.CellStyle = dicCellDateTimeStyle["DateTime"]; //格式化显示
					break;
				case "System.Boolean": //布尔型
					bool boolV = false;
					bool.TryParse(fieldValue, out boolV);
					cell.SetCellValue(boolV);
					break;
				case "System.Int16": //整型
				case "System.Int32":
				case "System.Int64":
				case "System.Byte":
					int intV = 0;
					int.TryParse(fieldValue, out intV);
					cell.SetCellValue(intV);
					break;
				case "System.Single":
					Single singleV = 0;
					Single.TryParse(fieldValue, out singleV);
					cell.SetCellValue(singleV);
					break;
				case "System.Decimal": //浮点型
				case "System.Double":
					double doubV = 0;
					double.TryParse(fieldValue, out doubV);
					cell.SetCellValue(doubV);
					break;
				case "System.DBNull": //空值处理
					cell.SetCellValue("");
					break;
				case "image"://图片
					cell.CellStyle.DataFormat = 0; //重置
					Image img = null;
					try
					{
						if (fieldValue.StartsWith("/Files") || fieldValue.StartsWith("/UploadFiles"))
						{
							using (FileStream fs = File.OpenRead(path + fieldValue))
							{
								img = Image.FromStream(fs);
							}
						}
					}
					catch (Exception ex)
					{
						break;
					}
					if (img == null) break;
					var height = img.Height;
					var width = img.Width;
					System.IO.MemoryStream ms = new System.IO.MemoryStream();
					img.Save(ms, System.Drawing.Imaging.ImageFormat.Png);
					byte[] buffer = ms.ToArray();
					int pictureIdx = workbook.AddPicture(buffer, PictureType.PNG);
					HSSFPatriarch patriarch = (HSSFPatriarch)workbook.GetSheetAt(0).CreateDrawingPatriarch();
					int X = 0;
					int Y = 0;
					//SetPoint(img,out X, out Y);
					HSSFClientAnchor anchor = new HSSFClientAnchor(X, Y, 0, 0, colIndex, rowindex, colIndex + 1, rowindex + 1);
					HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
					break;
				default:
					cell.SetCellValue("");
					break;
			}
		}
		static int SetPoint(Image img, out int X, out int Y)
		{
			var height = img.Height;
			var width = img.Width;
			X = 0;
			Y = 0;
			////float bili = heigh;
			if (height < 66 && width < 124)
			{
				return 0;
			}
			if (width / height > 124 / 66)
			{
				Y = 66 - height / width * 124;
				return 0;
			}
			else
			{
				X = 124 - width / height * 66;
				return 0;
			}
		}

		/// <summary>
		/// 获取数据源每列最大长度
		/// </summary>
		/// <param name="dtSource"></param>
		/// <returns></returns>
		static int[] GetDataColumnValueWidth(DataTable dtSource)
		{
			int[] arrColWidth = new int[dtSource.Columns.Count];
			foreach (DataColumn item in dtSource.Columns)
			{
				arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
			}
			int flagLength = dtSource.Rows.Count;
			for (int i = 0; i < flagLength; i++)
			{
				for (int j = 0; j < dtSource.Columns.Count; j++)
				{
					int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
					if (intTemp > arrColWidth[j])
					{
						arrColWidth[j] = intTemp;
					}
				}
			}
			return arrColWidth;
		}
		#endregion

	}
}
